# STATISTICS GENERATION SCRIPT
# This script performs a random draft for any team that does not have a roster
# by the time the league starts, or if the league creator selected a random draft.
# 
# Script must pass in the week you want to generate stats for.
#
# Created on November 13, 2009 by Brandon Fulco

#!/bin/bash
set +x
RANDOM=$(date '+%s')
mysql FBT52009 -ubroomball -pbroomball -horion.csl.mtu.edu > file.txt <<EOFMYSQL
SELECT PLAYER_ID FROM PLAYERS;
EOFMYSQL

for line in $(sed '1d' file.txt)
do
	goals=$(echo $[($RANDOM % 5)])
	saves=$(echo $[($RANDOM % 20)])
	assists=$(echo $[($RANDOM % 5)])
	score=$(echo "($goals*3)+($saves*2)+($assists)" | bc -l)
	mysql FBT52009 -ubroomball -pbroomball -horion.csl.mtu.edu <<EOFMYSQL
INSERT INTO STATS (PLAYER_ID, GOALS, SAVES, ASSISTS, SCORE, WEEK)
VALUES ('$line', '$goals', '$saves', '$assists', '$score', '$1');
EOFMYSQL

done

rm -f file.txt

mysql FBT52009 -ubroomball -pbroomball -horion.csl.mtu.edu > teams.txt <<EOFMYSQL
SELECT TEAM_ID FROM TEAMS;
EOFMYSQL

for team in $(sed '1d' teams.txt)
do 

	mysql FBT52009 -ubroomball -pbroomball -horion.csl.mtu.edu > player.txt <<EOFMYSQL
SELECT PLAYER_ID FROM ROSTER WHERE TEAM_ID='$team' AND POSITION != '-1';
EOFMYSQL

	touch score.txt
	
	for player in $(sed '1d' player.txt)
	do
		touch tmp.txt
		mysql FBT52009 -ubroomball -pbroomball -horion.csl.mtu.edu > tmp.txt <<EOFMYSQL
SELECT SCORE FROM STATS WHERE PLAYER_ID='$player' AND WEEK = '$1';
EOFMYSQL

		sed '1d' tmp.txt >> score.txt
		rm -f tmp.txt
	done
	total=
	for score in $(cat score.txt)
	do
		total=$(echo $[($total+$score)] | bc)
		#echo $total
	done

	mysql FBT52009 -ubroomball -pbroomball -horion.csl.mtu.edu <<EOFMYSQL
UPDATE GAMES
SET TEAM_1_SCORE='$total' 
WHERE TEAM1='$team' AND WEEK='$1';
EOFMYSQL

	mysql FBT52009 -ubroomball -pbroomball -horion.csl.mtu.edu <<EOFMYSQL
UPDATE GAMES
SET TEAM_2_SCORE='$total' 
WHERE TEAM2='$team' AND WEEK='$1';
EOFMYSQL

	rm -f player.txt
	rm -f score.txt
done

mysql FBT52009 -ubroomball -pbroomball -horion.csl.mtu.edu > games.txt <<EOFMYSQL
SELECT CONCAT(TEAM1,'|',TEAM2,'|',TEAM_1_SCORE,'|',TEAM_2_SCORE) AS '|'
FROM GAMES
WHERE WEEK = '$1'
EOFMYSQL

IFS='
'
cat games.txt
for line in $(sed '1d' games.txt)
do
    # Grab Team 1 ID
	team1=$(echo $line | awk -F\| '{print $1}')
	mysql FBT52009 -ubroomball -pbroomball -horion.csl.mtu.edu > team1.txt <<EOFMYSQL
SELECT CONCAT(WIN,'|',LOSS,'|',DRAW,'|',POINTS) AS '|'
FROM TEAMS
WHERE TEAM_ID = '$team1'
EOFMYSQL
	win1=$(sed '1d' team1.txt | awk -F\| '{print $1}')
	lose1=$(sed '1d' team1.txt | awk -F\| '{print $2}')
	draw1=$(sed '1d' team1.txt | awk -F\| '{print $3}')
	points1=$(sed '1d' team1.txt | awk -F\| '{print $4}')
	score1=$(echo $line | awk -F\| '{print $3}')
	team2=$(echo $line | awk -F\| '{print $2}')

	mysql FBT52009 -ubroomball -pbroomball -horion.csl.mtu.edu > team2.txt <<EOFMYSQL
SELECT CONCAT(WIN,'|',LOSS,'|',DRAW,'|',POINTS) AS '|'
FROM TEAMS
WHERE TEAM_ID = '$team2'
EOFMYSQL

	win2=$(sed '1d' team2.txt | awk -F\| '{print $1}')
	lose2=$(sed '1d' team2.txt | awk -F\| '{print $2}')
	draw2=$(sed '1d' team2.txt | awk -F\| '{print $3}')
	points2=$(sed '1d' team2.txt | awk -F\| '{print $4}')
	score2=$(echo $line | awk -F\| '{print $4}')
	
	if [ $score1 -gt $score2 ]
	then
		win1=$(echo "$[($win1+1)]" | bc)
		lose2=$(echo "$[($lose2+1)]" | bc)
	elif [ $score2 -gt $score1 ]
	then 	
		win2=$(echo "$[($win2+1)]" | bc)
		lose1=$(echo "$[($lose1+1)]" | bc)
	else
		draw1=$(echo "$[($draw1+1)]" | bc)
		draw2=$(echo "$[($draw2+1)]" | bc)
	fi
	points1=$(echo "$score1+$points1" | bc)
	points2=$(echo "$score2+$points2" | bc)
	pct1=$(echo "scale=2; $win1/($win1+$lose1+$draw1)" | bc)
	pct2=$(echo "scale=2; $win2/($win2+$lose2+$draw2)" | bc)
	
	mysql FBT52009 -ubroomball -pbroomball -horion.csl.mtu.edu <<EOFMYSQL
UPDATE TEAMS
SET WIN='$win1', LOSS='$lose1', DRAW='$draw1', POINTS='$points1', PCT='$pct1'
WHERE TEAM_ID = '$team1'
EOFMYSQL

	mysql FBT52009 -ubroomball -pbroomball -horion.csl.mtu.edu <<EOFMYSQL
UPDATE TEAMS
SET WIN='$win2', LOSS='$lose2', DRAW='$draw2', POINTS='$points2', PCT='$pct2'
WHERE TEAM_ID = '$team2'
EOFMYSQL

	rm -f team1.txt
	rm -f team2.txt
done

rm -f games.txt
